﻿Imports System.Net.Mail
Imports System.Text
Imports System.Net.NetworkCredential
Imports System.Data.SqlClient
Imports System.Data
Imports System.Collections.Generic

Partial Class driver_sick
    Inherits System.Web.UI.Page
	
    Dim connString As String = "Data Source=sql2008.campus.jcu.edu ; Initial Catalog=service; User Id=Thunder48; Password=Thundertech169;"

	Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        Dim parent = CType(Me.Master, driver_MasterPage)
        'Make sure it's not the admin
        If Session("driver_id") Is Nothing Then
            Session("loggedin") = False
            Response.Redirect("default.aspx")
        End If

        If IsPostBack Then
            classInformation()
        End If


        Try

            If IsPostBack = False Then
                Dim conn As New SqlConnection
                Dim cmd As String
                Dim sql As SqlCommand = New SqlCommand
                Dim reader As SqlDataReader
                conn.ConnectionString = connString
                conn.Open()
                'cmd = "select * from Event where DriverID = " & Session("driver_id")
                'cmd = "select * from COMSERV_ACTIVITY,Event where semester_yearid='" & Session("semester") & "' and needed = 'One-time Project' and COMSERV_ACTIVITY.activityid = Event.ServiceID and DriverID is NULL"
                cmd = "SELECT [ID], convert(varchar, Event.Date, 101) + ' - ' + [activity-name] AS namedate from Event INNER JOIN COMSERV_ACTIVITY ON Event.ServiceID = COMSERV_ACTIVITY.activityid where Event.DriverID =" & Session("driver_id") & " and Event.Date >= GETDATE() Order by Event.[Date] asc"

                sql.CommandText = cmd
                sql.Connection = conn
                reader = sql.ExecuteReader()
                If reader.HasRows Then
                    activityList.DataSource = reader
                    activityList.DataValueField = "ID"
                    activityList.DataTextField = ("namedate")
                    activityList.DataBind()
                End If

                conn.Close()
            End If
        Catch ex As Exception
            Response.Write(ex)
        End Try


    End Sub
	
    Protected Sub classInformation()

        'activityList.Items.Clear()
        Dim conn2 As New SqlConnection
        Dim cmd2 As String
        Dim activityid As String
        Dim sql2 As SqlCommand = New SqlCommand
        Dim classInfo As SqlDataReader
        conn2.ConnectionString = connString
        conn2.Open()
        Dim selection As String = activityList.SelectedValue
        'Response.Write(selection)
        'cmd2 = "select classInformation from nathan_test_classes where className='" & selection.ToString.Trim() & "'"
        'cmd2 = "select [description],[activityid],[begin_time],[end_time] from comserv_activity where [activityid]= (Select [ServiceID] from Event where ID =" & selection & ")"
        cmd2 = "Select Date from Event where ID =" & selection
        'Response.Write(cmd2)
        sql2.CommandText = cmd2
        sql2.Connection = conn2

        Try
            classInfo = sql2.ExecuteReader()
            If classInfo.Read() Then
                'Do While results.Read()
                'Response.Write(results.GetString(0))
                'activityList.Items.Add(results(0))
                'activityList.DataSource = classInfo
                'activityList.DataTextField = "className"
                'activityList.DataBind()
                'activityDetails.Text = classInfo.GetString(0)
                whichday.Text = "Substitute Request For: " & classInfo.GetDateTime(0).ToString("ddd, MMM d")
                'begintime.Text = classInfo.GetDateTime(2).ToString("h:mm:ss tt")
                'endtime.Text = classInfo.GetDateTime(3).ToString("h:mm:ss tt")



                'activityID.Text = classInfo.GetInt32(1)
                'Loop
            End If
        Catch ex As Exception
            'Response.Write(ex)
        End Try

    End Sub
	
    Protected Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submitButton.Click
        If activityList.SelectedIndex >= 0 Then
            UpdateEventTable(activityList.SelectedItem.Value) 'sets DefaultDriver to original Driver and Nulls DriverID
            PrepMail(activityList.SelectedItem.Value, TextBox1.Text)
            panel1.Visible = False
            panel2.Visible = True
        Else
            Response.Write("Please Select a Date! :)")
        End If


    End Sub

    Private Sub UpdateEventTable(ByVal eventid As Integer)
        Try
            Dim updatesql As String
            Dim updateConn As SqlConnection
            Dim updateCommand As SqlCommand

            updatesql = "UPDATE [Event] SET [DefaultDriverID] = @DefaultDriverID, [DriverID] = @DriverID WHERE ID = " & eventid
            updateConn = New SqlConnection(connString)
            updateCommand = New SqlCommand(updatesql, updateConn)
            'updateCommand.CommandType = update
            updateCommand.Parameters.AddWithValue("@DefaultDriverID", Session("driver_id"))
            updateCommand.Parameters.AddWithValue("@DriverID", DBNull.Value)
            updateCommand.Connection = updateConn
            updateConn.Open()
            updateCommand.ExecuteNonQuery()
            updateConn.Close()
        Catch ex As Exception
            'Response.Write(ex)
        End Try
    End Sub
    Private Sub PrepMail(ByVal eventid As Integer, ByVal body As String)


        Dim content As New StringBuilder

        content.Append("<table><tr><td></td></tr>")
        content.Append("<tr><td>Name: " & Session("first_name") & " " & Session("last_name") & "</td></tr>")

        content.Append("<tr><td>Email: " & Session("email") & "</td></tr>")
        content.Append("<tr><td>Comments: " & body & "</td></tr></table>")

        Dim connString As String = "Data Source=sql2008.campus.jcu.edu ; Initial Catalog=service; User Id=Thunder48; Password=Thundertech169;"
        Dim conn As SqlConnection
        Dim command As SqlCommand
        Dim adapter As New SqlDataAdapter
        Dim ds As New DataSet
        Dim sql As String
        Dim reader As SqlDataReader
        Dim starttime As String
        Dim endtime As String
        Dim drivetime As Integer


        sql = "select [activity-name], [begin_time], [end_time], [travel_time] from comserv_activity where [activityid] = ( select [ServiceID]  from [Event] where ID =" & eventid & ")"
        conn = New SqlConnection(connString)
        command = New SqlCommand(sql, conn)

        command.CommandText = sql
        command.Connection = conn

        Try
            conn.Open()
            reader = command.ExecuteReader()
            While reader.Read()
                content.Append("<tr><td>Activity: " & reader(0) & "</td></tr>")
                content.Append("<tr><td>" & whichday.Text & "</td></tr>")

                'begin with drive logic
                drivetime = reader(3)
                starttime = (reader.GetDateTime(1)).AddMinutes(-(drivetime)).ToString("hh:mm tt")
                endtime = (reader.GetDateTime(2)).AddMinutes(drivetime).ToString("hh:mm tt")
                content.Append("<tr><td>Time (with travel) : " & starttime & " to " & endtime & "</td></tr>")
                content.Append("<br><a href=""" & "http://143.105.15.245/driver/substitutefill.aspx?id=" & eventid & """ target= & """ & "_blank" & """>Fill in</a>")
                content.Append("</table>")
            End While
            conn.Close()
        Catch ex As Exception
            'Response.Write(ex)
        End Try


        'Send to logic --------------------------------------------

        Try


            Dim conn3 As New SqlConnection
            Dim cmd3 As String
            Dim sql3 As SqlCommand = New SqlCommand
            Dim reader3 As SqlDataReader
            Dim listofemails As New List(Of String)
            Dim email As String
            Dim emails As String
            conn3.ConnectionString = connString
            conn3.Open()
            cmd3 = "Select [EMail] from Driver"

            sql3.CommandText = cmd3
            sql3.Connection = conn3
            reader3 = sql3.ExecuteReader()
            While reader3.Read()
                email = reader3(0).ToString
                listofemails.Add(email)
            End While

            conn3.Close()

            SendMail(listofemails, content.ToString)

        Catch ex As Exception
            Response.Write(ex)


        End Try

        '----------------------------------------------------------




    End Sub

    Private Sub SendMail(ByVal emails As List(Of String), ByVal content As String)

        Dim mailServerName As String = "smtp.gmail.com"

        Dim mailAccount As New System.Net.NetworkCredential("cssaliaisons@jcu.edu", "driver2012")

        Dim message As MailMessage = New MailMessage("cssaliaisons@jcu.edu", "cssaliaisons@jcu.edu", "Notification: Student Driver Substitute Needed", content)
        Dim i As Integer
        For i = 0 To emails.Count - 1
            message.Bcc.Add(emails.Item(i))
        Next i

        '  message.Bcc.Add("mpratt13@jcu.edu")
        message.IsBodyHtml = True

        Dim mailClient As SmtpClient = New SmtpClient

        Try
            mailClient.Host = mailServerName
            mailClient.Port = 587
            mailClient.EnableSsl = True
            mailClient.UseDefaultCredentials = False
            mailClient.Credentials = mailAccount
            mailClient.Send(message)
        Catch ex As Exception
            'Response.Write(ex)
            Response.Write("Failure sending substitute notice.")
        End Try

        message.Dispose()
    End Sub
End Class
